﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Diagnostics;

namespace XBJCYGC
{
    public static class DB
    {
        public static OracleConnection CYGC
        {
            get { return new OracleConnection("data source=10.75.0.112:1521/orcl;password=XBJCYGC;user id=XBJCYGC"); }
        }

    }

    public static class LinqExt
    {
        public static void ForEach<T>(this IEnumerable<T> list, Action<T, int> action)
        {
            var i = 0;
            foreach (T one in list)
            {
                action(one, i);
                i++;
            }
        }
        public static void ForEach<T>(this IEnumerable<T> list, Action<T> action)
        {
            foreach (T one in list)
            {
                action(one);
            }

        }
    }
    /// <summary>
    /// 为所有对象增加导出json的方法。
    /// </summary>
    public static class JsonExtenions
    {
        public static string toJson(this object me)
        {
            return JsonConvert.SerializeObject(me);
        }
    }
    /// <summary>
    /// 为oracle的数据连接扩展执行查询、更新SQL的方法，及执行存储过程的方法
    /// </summary>
    public static class OracleConnectionExtenions
    {
        /// <summary>
        /// 返回datatable
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable Query(this OracleConnection conn, string sql, Array paras = null)
        {
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                OracleDataAdapter oda = new OracleDataAdapter(cmd);
                oda.Fill(dt);
                dt.TableName = "sheet";
                oda.Dispose();
                oda = null;
                cmd.Dispose();
                cmd = null;

            }
            catch(Exception e)
            {
                throw;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
                conn = null;
            }
            return dt;
        }
        /// <summary>
        /// 返回单个值。没有数据行，返回null.如果是null,则返回DBNULL.VALUE
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object QueryOneValue(this OracleConnection conn, string sql, Array paras = null)
        {
            object o = new object();
            try
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                o = cmd.ExecuteScalar();
                cmd.Dispose();
                cmd = null;
            }
            catch (Exception e)
            { throw; }
            finally
            {
                conn.Close();
                conn.Dispose();
                conn = null;
            }
            return o;
        }
        /// <summary>
        /// 单个数值的情况
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static decimal QueryOneDecimal(this OracleConnection conn, string sql, Array paras = null)
        {
            return Convert.ToDecimal(QueryOneValue(conn, sql, paras));
        }
        /// <summary>
        /// 返回影响行数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int Execute(this OracleConnection conn, string sql, Array paras = null)
        {
            int r = -1;
            try
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                r = cmd.ExecuteNonQuery();
                cmd.Dispose();
                cmd = null;
            }
            catch(Exception e)
            { throw; }
            finally
            {
                conn.Close(); conn.Dispose();
                conn = null;
            }
            return r;
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="proName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int Procedure(this OracleConnection conn, string proName, Array paras = null)
        {
            int r = -1;
            try
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = proName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                r = cmd.ExecuteNonQuery();
                cmd.Dispose();
                cmd = null;
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                conn.Close(); conn.Dispose();
                conn = null;
            }
            return r;
        }

        public static DataTable ProcedureReturnDt(this OracleConnection conn, string proName, Array paras = null)
        {
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = proName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                OracleDataAdapter oda = new OracleDataAdapter(cmd);
                oda.Fill(dt);
                oda.Dispose();
                oda = null;
                cmd.Dispose();
                cmd = null;
            }
            catch (Exception e)
            { throw; }
            finally
            {
                conn.Close(); conn.Dispose();
                conn = null;
            }
            return dt;
        }

        /// <summary>
        /// 过程可以返回多个datatable到一个ds中，所以增加这个方法
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="proName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataSet ProcedureReturnDs(this OracleConnection conn, string proName, Array paras = null)
        {
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = proName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                OracleDataAdapter oda = new OracleDataAdapter(cmd);
                oda.Fill(ds);
                oda.Dispose();
                oda = null;
                cmd.Dispose();
                cmd = null;
            }
            catch (Exception e) { throw; }
            finally
            {
                conn.Close(); conn.Dispose();
                conn = null;
            }
            return ds;
        }
    }
}

/* 更新删除的方法
 * OracleConnection _connObj = new OracleConnection(_connstring);  
 * _connObj.Open();        
 * OracleCommand _commandObj = _connObj.CreateCommand(); 
 * OracleDataAdapter _adapterObj = new OracleDataAdapter(_commandObj); 
 * //Manually define the UPDATE command in the OracleDataAdapter          
 * _sql = "UPDATE Products SET Name=:Name, Price=:Price, Remarks=:Remarks WHERE     ID=:ID";   
 * _adapterObj.UpdateCommand = new OracleCommand(_sql, _connObj);         
 * _adapterObj.UpdateCommand.Parameters.Add (new OracleParameter("Name",     OracleDbType.Varchar2, 255, "Name")); 
 * _adapterObj.UpdateCommand.Parameters.Add (new OracleParameter("Price",     OracleDbType.Decimal, 10, "Price")); 

 * _adapterObj.UpdateCommand.Parameters.Add (new OracleParameter("Remarks",     OracleDbType.Varchar2, 4000, "Remarks"));  
 * _adapterObj.UpdateCommand.Parameters.Add (new OracleParameter("ID",     OracleDbType.Varchar2, 10, "ID"));  
 * 
//Manually define the INSERT command in the OracleDataAdapter       
 * _sql = "INSERT INTO Products(Name, Price, Remarks, ID) VALUES(:Name, :Price,     :Remarks, :ID)"; 
 * _adapterObj.InsertCommand = new OracleCommand(_sql, _connObj);             
 * _adapterObj.InsertCommand.Parameters.Add(new OracleParameter("Name",     OracleDbType.Varchar2, 255, "Name"));  
 * _adapterObj.InsertCommand.Parameters.Add(new OracleParameter("Price",     OracleDbType.Decimal, 10, "Price"));    
 * _adapterObj.InsertCommand.Parameters.Add(new OracleParameter("Remarks",     OracleDbType.Varchar2, 4000, "Remarks"));        
 * _adapterObj.InsertCommand.Parameters.Add(new OracleParameter("ID",     OracleDbType.Varchar2, 10, "ID"));  
 
 * //Manually define the DELETE command in the OracleDataAdapter          
 * _sql = "DELETE FROM Products WHERE ID=:ID";                
 * _adapterObj.DeleteCommand = new OracleCommand(_sql, _connObj);      
 * _adapterObj.DeleteCommand.Parameters.Add(new OracleParameter("ID",     OracleDbType.Varchar2, 10, "ID"));  

 * //Now we pass in the dataset to the DataAdapter and request it   
 * //to commit the changes to the database (using the command objects above)      
 * _adapterObj.Update(_ds);        
 * _connObj.Close();         
 * _connObj.Dispose();     
 * _connObj = null;     
 * MessageBox.Show("Dataset committed!");  
 
 
 
  OracleCommandBuilder _commBldrObj = new OracleCommandBuilder(_adapterObj); 
 _ds.Tables[0].Columns["ID"].Unique = true;   之后就可以直接用adapter进行update了
 
 传递关联数组作为参数：
 * OracleParameter _priceParam = new OracleParameter();
 * _priceParam.ParameterName = "ProdPrices"; 
 * _priceParam.OracleDbType = OracleDbType.Decimal;
 * _priceParam.Direction = ParameterDirection.Input;  
//Declare the parameter as a PL/SQL Associative array
 * _priceParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;  
//Create the array 
 * Decimal [] decArray= new Decimal[3];
 * decArray[0] = 100; decArray[1] = 300; decArray[2] = 500;  
//Pass it to the parameter object 
 * _priceParam.Value = decArray;
 * _cmdObj.Parameters.Add(_priceParam)
 
 * //获取返回的关联数据
 *  OracleString[] stringArray = (OracleString[])_priceParam.Value; 
 *  for (_counter = 0; _counter <= stringArray.GetUpperBound(0); _counter++)   
 *  {                   
 *  OracleString _outputString = stringArray[_counter];         
 *  _result = _result + _outputString.Value + "\n";   
 *  } 
 *  
 * 传递refcursor:
 *  OracleParameter _RefParam = new OracleParameter(); 
 *  _RefParam.ParameterName = "ProdInfo";          
 *  _RefParam.OracleDbType = OracleDbType.RefCursor; 
 *  _RefParam.Direction = ParameterDirection.Output;       
 *  _cmdObj.Parameters.Add(_RefParam);       
 *  OracleDataReader _rdrObj = _cmdObj.ExecuteReader(); 
 *  1、返回 reader
 * if (_rdrObj.HasRows)  
 * {           
 *      while (_rdrObj.Read())  ........
 *  2、或者返回dataset
 *   OracleDataAdapter _adapterObj = new OracleDataAdapter(_cmdObj);      
 *   DataSet _datasetObj = new DataSet ();   
 *   _adapterObj.Fill(_datasetObj);            
 */
